{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#!/usr/bin/env python\n",
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "\"\"\"\n",
    "orm模块设计的原因：\n",
    "    1. 简化操作\n",
    "        sql操作的数据是 关系型数据， 而python操作的是对象，为了简化编程 所以需要对他们进行映射\n",
    "        映射关系为：\n",
    "            表 ==>  类\n",
    "            行 ==> 实例\n",
    "设计orm接口：\n",
    "    1. 设计原则：\n",
    "        根据上层调用者设计简单易用的API接口\n",
    "    2. 设计调用接口\n",
    "        1. 表 <==> 类\n",
    "            通过类的属性 来映射表的属性（表名，字段名， 字段属性）\n",
    "                from transwarp.orm import Model, StringField, IntegerField\n",
    "                class User(Model):\n",
    "                    __table__ = 'users'\n",
    "                    id = IntegerField(primary_key=True)\n",
    "                    name = StringField()\n",
    "            从中可以看出 __table__ 拥有映射表名， id/name 用于映射 字段对象（字段名 和 字段属性）\n",
    "        2. 行 <==> 实例\n",
    "            通过实例的属性 来映射 行的值\n",
    "                # 创建实例:\n",
    "                user = User(id=123, name='Michael')\n",
    "                # 存入数据库:\n",
    "                user.insert()\n",
    "            最后 id/name 要变成 user实例的属性\n",
    "\"\"\"\n",
    "\n",
    "import db\n",
    "import time\n",
    "import logging\n",
    "\n",
    "\n",
    "_triggers = frozenset(['pre_insert', 'pre_update', 'pre_delete'])\n",
    "\n",
    "\n",
    "def _gen_sql(table_name, mappings):\n",
    "    \"\"\"\n",
    "    类 ==> 表时 生成创建表的sql\n",
    "    \"\"\"\n",
    "    pk = None\n",
    "    sql = ['-- generating SQL for %s:' % table_name, 'create table `%s` (' % table_name]\n",
    "    for f in sorted(mappings.values(), lambda x, y: cmp(x._order, y._order)):\n",
    "        if not hasattr(f, 'ddl'):\n",
    "            raise StandardError('no ddl in field \"%s\".' % f)\n",
    "        ddl = f.ddl\n",
    "        nullable = f.nullable\n",
    "        if f.primary_key:\n",
    "            pk = f.name\n",
    "        #sql.append(nullable and '  `%s` %s,' % (f.name, ddl) or '  `%s` %s not null,' % (f.name, ddl))\n",
    "        sql.append('  `%s` %s,' % (f.name, ddl) if nullable else '  `%s` %s not null,' % (f.name, ddl))\n",
    "    sql.append('  primary key(`%s`)' % pk)\n",
    "    sql.append(');')\n",
    "    return '\\n'.join(sql)\n",
    "\n",
    "\n",
    "class Field(object):\n",
    "    \"\"\"\n",
    "    保存数据库中的表的  字段属性\n",
    "    _count: 类属性，没实例化一次，该值就+1\n",
    "    self._order: 实例属性， 实例化时从类属性处得到，用于记录 该实例是 该类的第多少个实例\n",
    "        例如最后的doctest：\n",
    "            定义user时该类进行了5次实例化，来保存字段属性\n",
    "                id = IntegerField(primary_key=True)\n",
    "                name = StringField()\n",
    "                email = StringField(updatable=False)\n",
    "                passwd = StringField(default=lambda: '******')\n",
    "                last_modified = FloatField()\n",
    "            最后各实例的_order 属性就是这样的\n",
    "                INFO:root:[TEST _COUNT] name => 1\n",
    "                INFO:root:[TEST _COUNT] passwd => 3\n",
    "                INFO:root:[TEST _COUNT] id => 0\n",
    "                INFO:root:[TEST _COUNT] last_modified => 4\n",
    "                INFO:root:[TEST _COUNT] email => 2\n",
    "            最后生成__sql时（见_gen_sql 函数），这些字段就是按序排列\n",
    "                create table `user` (\n",
    "                `id` bigint not null,\n",
    "                `name` varchar(255) not null,\n",
    "                `email` varchar(255) not null,\n",
    "                `passwd` varchar(255) not null,\n",
    "                `last_modified` real not null,\n",
    "                primary key(`id`)\n",
    "                );\n",
    "    self._default: 用于让orm自己填入缺省值，缺省值可以是 可调用对象，比如函数\n",
    "                比如：passwd 字段 <StringField:passwd,varchar(255),default(<function <lambda> at 0x0000000002A13898>),UI>\n",
    "                     这里passwd的默认值 就可以通过 返回的函数 调用取得\n",
    "    其他的实例属性都是用来描述字段属性的\n",
    "    \"\"\"\n",
    "    _count = 0\n",
    "\n",
    "    def __init__(self, **kw):\n",
    "        self.name = kw.get('name', None)\n",
    "        self._default = kw.get('default', None)\n",
    "        self.primary_key = kw.get('primary_key', False)\n",
    "        self.nullable = kw.get('nullable', False)\n",
    "        self.updatable = kw.get('updatable', True)\n",
    "        self.insertable = kw.get('insertable', True)\n",
    "        self.ddl = kw.get('ddl', '')\n",
    "        self._order = Field._count\n",
    "        Field._count += 1\n",
    "\n",
    "    @property\n",
    "    def default(self):\n",
    "        \"\"\"\n",
    "        利用getter实现的一个写保护的 实例属性\n",
    "        \"\"\"\n",
    "        d = self._default\n",
    "        return d() if callable(d) else d\n",
    "\n",
    "    def __str__(self):\n",
    "        \"\"\"\n",
    "        返回实例对象的描述信息，比如：\n",
    "            <IntegerField:id,bigint,default(0),UI>\n",
    "            类：实例：实例ddl属性：实例default信息，3中标志位：N U I\n",
    "        \"\"\"\n",
    "        s = ['<%s:%s,%s,default(%s),' % (self.__class__.__name__, self.name, self.ddl, self._default)]\n",
    "        self.nullable and s.append('N')\n",
    "        self.updatable and s.append('U')\n",
    "        self.insertable and s.append('I')\n",
    "        s.append('>')\n",
    "        return ''.join(s)\n",
    "\n",
    "\n",
    "class StringField(Field):\n",
    "    \"\"\"\n",
    "    保存String类型字段的属性\n",
    "    \"\"\"\n",
    "    def __init__(self, **kw):\n",
    "        if 'default' not in kw:\n",
    "            kw['default'] = ''\n",
    "        if 'ddl' not in kw:\n",
    "            kw['ddl'] = 'varchar(255)'\n",
    "        super(StringField, self).__init__(**kw)\n",
    "\n",
    "\n",
    "class IntegerField(Field):\n",
    "    \"\"\"\n",
    "    保存Integer类型字段的属性\n",
    "    \"\"\"\n",
    "    def __init__(self, **kw):\n",
    "        if 'default' not in kw:\n",
    "            kw['default'] = 0\n",
    "        if 'ddl' not in kw:\n",
    "            kw['ddl'] = 'bigint'\n",
    "        super(IntegerField, self).__init__(**kw)\n",
    "\n",
    "\n",
    "class FloatField(Field):\n",
    "    \"\"\"\n",
    "    保存Float类型字段的属性\n",
    "    \"\"\"\n",
    "    def __init__(self, **kw):\n",
    "        if 'default' not in kw:\n",
    "            kw['default'] = 0.0\n",
    "        if 'ddl' not in kw:\n",
    "            kw['ddl'] = 'real'\n",
    "        super(FloatField, self).__init__(**kw)\n",
    "\n",
    "\n",
    "class BooleanField(Field):\n",
    "    \"\"\"\n",
    "    保存BooleanField类型字段的属性\n",
    "    \"\"\"\n",
    "    def __init__(self, **kw):\n",
    "        if not 'default' in kw:\n",
    "            kw['default'] = False\n",
    "        if not 'ddl' in kw:\n",
    "            kw['ddl'] = 'bool'\n",
    "        super(BooleanField, self).__init__(**kw)\n",
    "\n",
    "class TextField(Field):\n",
    "    \"\"\"\n",
    "    保存Text类型字段的属性\n",
    "    \"\"\"\n",
    "    def __init__(self, **kw):\n",
    "        if 'default' not in kw:\n",
    "            kw['default'] = ''\n",
    "        if 'ddl' not in kw:\n",
    "            kw['ddl'] = 'text'\n",
    "        super(TextField, self).__init__(**kw)\n",
    "\n",
    "\n",
    "class BlobField(Field):\n",
    "    \"\"\"\n",
    "    保存Blob类型字段的属性\n",
    "    \"\"\"\n",
    "    def __init__(self, **kw):\n",
    "        if 'default' not in kw:\n",
    "            kw['default'] = ''\n",
    "        if 'ddl' not in kw:\n",
    "            kw['ddl'] = 'blob'\n",
    "        super(BlobField, self).__init__(**kw)\n",
    "\n",
    "\n",
    "class VersionField(Field):\n",
    "    \"\"\"\n",
    "    保存Version类型字段的属性\n",
    "    \"\"\"\n",
    "    def __init__(self, name=None):\n",
    "        super(VersionField, self).__init__(name=name, default=0, ddl='bigint')\n",
    "\n",
    "\n",
    "class ModelMetaclass(type):\n",
    "    \"\"\"\n",
    "    对类对象动态完成以下操作\n",
    "    避免修改Model类：\n",
    "        1. 排除对Model类的修改\n",
    "    属性与字段的mapping：\n",
    "        1. 从类的属性字典中提取出 类属性和字段类 的mapping\n",
    "        2. 提取完成后移除这些类属性，避免和实例属性冲突\n",
    "        3. 新增\"__mappings__\" 属性，保存提取出来的mapping数据\n",
    "    类和表的mapping：\n",
    "        1. 提取类名，保存为表名，完成简单的类和表的映射\n",
    "        2. 新增\"__table__\"属性，保存提取出来的表名\n",
    "    \"\"\"\n",
    "    def __new__(cls, name, bases, attrs):\n",
    "        # skip base Model class:\n",
    "        if name == 'Model':\n",
    "            return type.__new__(cls, name, bases, attrs)\n",
    "\n",
    "        # store all subclasses info:\n",
    "        if not hasattr(cls, 'subclasses'):\n",
    "            cls.subclasses = {}\n",
    "        if not name in cls.subclasses:\n",
    "            cls.subclasses[name] = name\n",
    "        else:\n",
    "            logging.warning('Redefine class: %s' % name)\n",
    "\n",
    "        logging.info('Scan ORMapping %s...' % name)\n",
    "        mappings = dict()\n",
    "        primary_key = None\n",
    "        for k, v in attrs.iteritems():\n",
    "            if isinstance(v, Field):\n",
    "                if not v.name:\n",
    "                    v.name = k\n",
    "                logging.info('[MAPPING] Found mapping: %s => %s' % (k, v))\n",
    "                # check duplicate primary key:\n",
    "                if v.primary_key:\n",
    "                    if primary_key:\n",
    "                        raise TypeError('Cannot define more than 1 primary key in class: %s' % name)\n",
    "                    if v.updatable:\n",
    "                        logging.warning('NOTE: change primary key to non-updatable.')\n",
    "                        v.updatable = False\n",
    "                    if v.nullable:\n",
    "                        logging.warning('NOTE: change primary key to non-nullable.')\n",
    "                        v.nullable = False\n",
    "                    primary_key = v\n",
    "                mappings[k] = v\n",
    "        # check exist of primary key:\n",
    "        if not primary_key:\n",
    "            raise TypeError('Primary key not defined in class: %s' % name)\n",
    "        for k in mappings.iterkeys():\n",
    "            attrs.pop(k)\n",
    "        if not '__table__' in attrs:\n",
    "            attrs['__table__'] = name.lower()\n",
    "        attrs['__mappings__'] = mappings\n",
    "        attrs['__primary_key__'] = primary_key\n",
    "        attrs['__sql__'] = lambda self: _gen_sql(attrs['__table__'], mappings)\n",
    "        for trigger in _triggers:\n",
    "            if not trigger in attrs:\n",
    "                attrs[trigger] = None\n",
    "        return type.__new__(cls, name, bases, attrs)\n",
    "\n",
    "\n",
    "class Model(dict):\n",
    "    \"\"\"\n",
    "    这是一个基类，用户在子类中 定义映射关系， 因此我们需要动态扫描子类属性 ，\n",
    "    从中抽取出类属性， 完成 类 <==> 表 的映射， 这里使用 metaclass 来实现。\n",
    "    最后将扫描出来的结果保存在成类属性\n",
    "        \"__table__\" : 表名\n",
    "        \"__mappings__\": 字段对象(字段的所有属性，见Field类)\n",
    "        \"__primary_key__\": 主键字段\n",
    "        \"__sql__\": 创建表时执行的sql\n",
    "    子类在实例化时，需要完成 实例属性 <==> 行值 的映射， 这里使用 定制dict 来实现。\n",
    "        Model 从字典继承而来，并且通过\"__getattr__\",\"__setattr__\"将Model重写，\n",
    "        使得其像javascript中的 object对象那样，可以通过属性访问 值比如 a.key = value\n",
    "    >>> class User(Model):\n",
    "    ...     id = IntegerField(primary_key=True)\n",
    "    ...     name = StringField()\n",
    "    ...     email = StringField(updatable=False)\n",
    "    ...     passwd = StringField(default=lambda: '******')\n",
    "    ...     last_modified = FloatField()\n",
    "    ...     def pre_insert(self):\n",
    "    ...         self.last_modified = time.time()\n",
    "    >>> u = User(id=10190, name='Michael', email='orm@db.org')\n",
    "    >>> r = u.insert()\n",
    "    >>> u.email\n",
    "    'orm@db.org'\n",
    "    >>> u.passwd\n",
    "    '******'\n",
    "    >>> u.last_modified > (time.time() - 2)\n",
    "    True\n",
    "    >>> f = User.get(10190)\n",
    "    >>> f.name\n",
    "    u'Michael'\n",
    "    >>> f.email\n",
    "    u'orm@db.org'\n",
    "    >>> f.email = 'changed@db.org'\n",
    "    >>> r = f.update() # change email but email is non-updatable!\n",
    "    >>> len(User.find_all())\n",
    "    1\n",
    "    >>> g = User.get(10190)\n",
    "    >>> g.email\n",
    "    u'orm@db.org'\n",
    "    >>> r = g.delete()\n",
    "    >>> len(db.select('select * from user where id=10190'))\n",
    "    0\n",
    "    >>> import json\n",
    "    >>> print User().__sql__()\n",
    "    -- generating SQL for user:\n",
    "    create table `user` (\n",
    "      `id` bigint not null,\n",
    "      `name` varchar(255) not null,\n",
    "      `email` varchar(255) not null,\n",
    "      `passwd` varchar(255) not null,\n",
    "      `last_modified` real not null,\n",
    "      primary key(`id`)\n",
    "    );\n",
    "    \"\"\"\n",
    "    __metaclass__ = ModelMetaclass\n",
    "\n",
    "    def __init__(self, **kw):\n",
    "        super(Model, self).__init__(**kw)\n",
    "\n",
    "    def __getattr__(self, key):\n",
    "        \"\"\"\n",
    "        get时生效，比如 a[key],  a.get(key)\n",
    "        get时 返回属性的值\n",
    "        \"\"\"\n",
    "        try:\n",
    "            return self[key]\n",
    "        except KeyError:\n",
    "            raise AttributeError(r\"'Dict' object has no attribute '%s'\" % key)\n",
    "\n",
    "    def __setattr__(self, key, value):\n",
    "        \"\"\"\n",
    "        set时生效，比如 a[key] = value, a = {'key1': value1, 'key2': value2}\n",
    "        set时添加属性\n",
    "        \"\"\"\n",
    "        self[key] = value\n",
    "\n",
    "    @classmethod\n",
    "    def get(cls, pk):\n",
    "        \"\"\"\n",
    "        Get by primary key.\n",
    "        \"\"\"\n",
    "        d = db.select_one('select * from %s where %s=?' % (cls.__table__, cls.__primary_key__.name), pk)\n",
    "        return cls(**d) if d else None\n",
    "\n",
    "    @classmethod\n",
    "    def find_first(cls, where, *args):\n",
    "        \"\"\"\n",
    "        通过where语句进行条件查询，返回1个查询结果。如果有多个查询结果\n",
    "        仅取第一个，如果没有结果，则返回None\n",
    "        \"\"\"\n",
    "        d = db.select_one('select * from %s %s' % (cls.__table__, where), *args)\n",
    "        return cls(**d) if d else None\n",
    "\n",
    "    @classmethod\n",
    "    def find_all(cls, *args):\n",
    "        \"\"\"\n",
    "        查询所有字段， 将结果以一个列表返回\n",
    "        \"\"\"\n",
    "        L = db.select('select * from `%s`' % cls.__table__)\n",
    "        return [cls(**d) for d in L]\n",
    "\n",
    "    @classmethod\n",
    "    def find_by(cls, where, *args):\n",
    "        \"\"\"\n",
    "        通过where语句进行条件查询，将结果以一个列表返回\n",
    "        \"\"\"\n",
    "        L = db.select('select * from `%s` %s' % (cls.__table__, where), *args)\n",
    "        return [cls(**d) for d in L]\n",
    "\n",
    "    @classmethod\n",
    "    def count_all(cls):\n",
    "        \"\"\"\n",
    "        执行 select count(pk) from table语句，返回一个数值\n",
    "        \"\"\"\n",
    "        return db.select('select count(`%s`) from `%s`' % (cls.__primay_key__.name, cls.__table__))\n",
    "\n",
    "    @classmethod\n",
    "    def count_by(cls, where, *args):\n",
    "        \"\"\"\n",
    "        通过select count(pk) from table where ...语句进行查询， 返回一个数值\n",
    "        \"\"\"\n",
    "        return db.select_int('select count(`%s`) from `%s` %s' % (cls.__primary_key__.name, cls.__table__, where), *args)\n",
    "\n",
    "    def update(self):\n",
    "        \"\"\"\n",
    "        如果该行的字段属性有 updatable，代表该字段可以被更新\n",
    "        用于定义的表（继承Model的类）是一个 Dict对象，键值会变成实例的属性\n",
    "        所以可以通过属性来判断 用户是否定义了该字段的值\n",
    "            如果有属性， 就使用用户传入的值\n",
    "            如果无属性， 则调用字段对象的 default属性传入\n",
    "            具体见 Field类 的 default 属性\n",
    "        通过的db对象的update接口执行SQL\n",
    "            SQL: update `user` set `passwd`=%s,`last_modified`=%s,`name`=%s where id=%s,\n",
    "                 ARGS: (u'******', 1441878476.202391, u'Michael', 10190\n",
    "        \"\"\"\n",
    "        self.pre_update and self.pre_update()\n",
    "        L = []\n",
    "        args = []\n",
    "        for k, v in self.__mappings__.iteritems():\n",
    "            if v.updatable:\n",
    "                if hasattr(self, k):\n",
    "                    arg = getattr(self, k)\n",
    "                else:\n",
    "                    arg = v.default\n",
    "                    setattr(self, k, arg)\n",
    "                L.append('`%s`=?' % k)\n",
    "                args.append(arg)\n",
    "        pk = self.__primary_key__.name\n",
    "        args.append(getattr(self, pk))\n",
    "        db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)\n",
    "        return self\n",
    "\n",
    "    def delete(self):\n",
    "        \"\"\"\n",
    "        通过db对象的 update接口 执行SQL\n",
    "            SQL: delete from `user` where `id`=%s, ARGS: (10190,)\n",
    "        \"\"\"\n",
    "        self.pre_delete and self.pre_delete()\n",
    "        pk = self.__primary_key__.name\n",
    "        args = (getattr(self, pk), )\n",
    "        db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)\n",
    "        return self\n",
    "\n",
    "    def insert(self):\n",
    "        \"\"\"\n",
    "        通过db对象的insert接口执行SQL\n",
    "            SQL: insert into `user` (`passwd`,`last_modified`,`id`,`name`,`email`) values (%s,%s,%s,%s,%s),\n",
    "            　　　　　 ARGS: ('******', 1441878476.202391, 10190, 'Michael', 'orm@db.org')\n",
    "        \"\"\"\n",
    "        self.pre_insert and self.pre_insert()\n",
    "        params = {}\n",
    "        for k, v in self.__mappings__.iteritems():\n",
    "            if v.insertable:\n",
    "                if not hasattr(self, k):\n",
    "                    setattr(self, k, v.default)\n",
    "                params[v.name] = getattr(self, k)\n",
    "        db.insert('%s' % self.__table__, **params)\n",
    "        return self\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    logging.basicConfig(level=logging.DEBUG)\n",
    "    db.create_engine('www-data', 'www-data', 'test', '192.168.10.128')\n",
    "    db.update('drop table if exists user')\n",
    "    db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')\n",
    "    import doctest\n",
    "    doctest.testmod()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
